package cn.itsource.utils;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;

//JDBC工具类
public class JDBCUtils {

    private static String driver="com.mysql.jdbc.Driver";//mysql连接驱动
    private static String url="jdbc:mysql://localhost:3306/account";//mysql数据库连接URL
    private static String user="root";//mysql数据库账号
    private static String password="colin123";//mysql数据库密码

    private static int user_id = 1001;//当前登录用户的ID（仅供测试使用）

    private JDBCUtils(){}

    //查询用户记账详细记录信息
    public static List<Map<String, Object>> getUserAccountDetail(Map<String, Object> param) {
        List<Map<String, Object>> userAccountDetailList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = JDBCUtils.getConnection();

            // 编写sql
            String sql = "select t.id, t.create_time, DATE_FORMAT(t.create_time,'%m月%d日') as timeStr, t.user_id, t.account_type, t.account_desc, t.account_num, t.week_str from t_account t where t.user_id = ? and YEAR(t.create_time) = ? and MONTH(t.create_time) = ?";

            // 创建语句执行者
            st= conn.prepareStatement(sql);

            //设置参数
            st.setInt(1, user_id);
            st.setString(2, param.get("current_year").toString());
            st.setString(3, param.get("current_month").toString());

            // 执行sql
            rs = st.executeQuery();

            Map<String, Object> map = null;
            //处理结果集
            while(rs.next()) {  // 遍历结果集ResultSet
                map = new HashMap<>();
                map.put("id", rs.getInt("id"));
                map.put("create_time", rs.getDate("create_time"));
                map.put("time", rs.getString("timeStr"));
                map.put("user_id", rs.getInt("user_id"));
                map.put("account_type", rs.getInt("account_type"));
                map.put("account_desc", rs.getString("account_desc"));
                map.put("account_num", rs.getBigDecimal("account_num"));
                map.put("week", rs.getString("week_str"));
                userAccountDetailList.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.colseResource(conn, st, rs);
        }
        return userAccountDetailList;
    }

    /**
     * 获取某用户某月的总收入或总支出
     * @param map
     * @return BigDecimal
     */
    public static BigDecimal getUserAccount(Map<String, Object> map){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        BigDecimal result = new BigDecimal(0);
        try {
            // 获取连接
            conn = JDBCUtils.getConnection();

            // 编写sql
            String sql = "select sum(t.account_num) as accountNum from t_account t where t.user_id = ? and YEAR(t.create_time) = ? and MONTH(t.create_time) = ? and t.account_type = ? order by t.create_time desc";

            // 创建语句执行者
            st= conn.prepareStatement(sql);

            //设置参数
            st.setInt(1, user_id);
            st.setString(2, map.get("current_year").toString());
            st.setString(3, map.get("current_month").toString());
            st.setInt(4, Integer.valueOf(map.get("account_type").toString()));

            // 执行sql
            rs = st.executeQuery();

            //处理结果集
            while(rs.next()) {  // 遍历结果集ResultSet
                //获取这条记录中每列数据,使用ResultSet接口的方法 getXX方法,参数建议写String列名
                result = rs.getBigDecimal("accountNum");  // 参数表示列名
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.colseResource(conn, st, rs);
        }
        return result == null ? new BigDecimal(0) : result;
    }

    /**
     * 向 t_account 表添加数据
     * @param map
     */
    public static void addAccount(Map<String, Object> map) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            // 获取连接
            conn = getConnection();

            // 编写sql
            String sql = "insert into t_account(create_time, user_id, account_type, account_desc, account_num, week_str) values(?,?,?,?,?,?)";

            // 创建语句执行者
            st= conn.prepareStatement(sql);

            //设置参数
            st.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
            st.setInt(2, user_id);
            st.setInt(3, Integer.valueOf(map.get("account_type").toString()));
            st.setString(4, map.get("account_desc").toString());
            st.setBigDecimal(5, new BigDecimal(map.get("account_num").toString()));
            st.setString(6, getCurrentYear());

            // 执行sql
            st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.colseResource(conn, st, rs);
        }
    }

    //获取当前年份
    public static String getCurrentYear() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        c.setTime(new java.util.Date());
        int year = c.get(Calendar.YEAR);
        return year+"";
    }

    static {
        /**
         * 驱动注册
         */
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }

    }

    /**
     * 获取 Connetion
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void colseResource(Connection conn,Statement st,ResultSet rs) {
        closeResultSet(rs);
        closeStatement(st);
        closeConnection(conn);
    }

    /**
     * 释放连接 Connection
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if(conn !=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        conn = null;
    }

    /**
     * 释放语句执行者 Statement
     * @param st
     */
    public static void closeStatement(Statement st) {
        if(st !=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        st = null;
    }

    /**
     * 释放结果集 ResultSet
     * @param rs
     */
    public static void closeResultSet(ResultSet rs) {
        if(rs !=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        rs = null;
    }

}